import pandas as pd
from datetime import date
import numpy as np
# dtype={'Date': date} 导入时指定数据类型
pd.options.display.max_columns=999
orders = pd.read_excel('Orders.xlsx', dtype={'Date': date})
# print(orders.shape)
# print(orders.columns)
# print(orders)
# 添加年份的列
#orders['Year']=pd.DatatimeIndex(orders['Date']).year
orders['Year']=pd.DatetimeIndex(orders.Date).year
# pd.DataFrame
print(orders.head(6))
# 透视表pivot_table,方法一。
pt1=orders.pivot_table(index='Category',columns='Year',values='Total',aggfunc=np.sum)
print(pt1)
groups=orders.groupby(['Category','Year'])
s=groups['Total'].sum()
c=groups['ID'].count()
pt2=pd.DataFrame({'Sum':s,'Count':c})
print(pt2)


